import xlrd
import xlwt

#分别打开excel
data = xlrd.open_workbook("E:\\bigData\\dataset\\wine1.xlsx")
data2 = xlrd.open_workbook("E:\\bigData\\dataset\\wine2.xlsx")

#分别读取wine1wine2的数据
table = data.sheet_by_name("Sheet1")
table2 = data2.sheet_by_name("Sheet1")

#分别读取行数
nrows = table.nrows
nrows2 = table2.nrows

#定义结果存储集合，存储从excel读出来的数
result = []
result2 = []

#遍历excel
for i in range(1, nrows):
    # 行的数据放在数组里
    rows = table.row_values(i)
    row2 = table2.row_values(i)

    #获取从excel中读出的数据，并根据要求处理
    Alcohol = rows[0]/100 #把结果映射到[0,1]之间
    Malic_acid = rows[1]
    Ash = rows[2]
    Alcalinity_of_ash = rows[3]
    Weight = rows[4]
    Total_phenols = row2[0]
    Flavanoids = row2[1]
    Nonflavanoid_phenols = row2[2]
    Proanthocyanins = row2[3]
    Color_intensity = row2[4]
    Hue = row2[5]
    diluted_wines = row2[6]
    Proline = row2[7]/10000 #把结果映射到[0,1]之间
    # 规约Alcohol（%）和 Weight（g）两个属性
    a = round((Alcohol * Weight), 2)

    #将处理好的结果集添加到列表中
    result.append(
        [Alcohol,
         Malic_acid,
         Ash,
         Alcalinity_of_ash,
         Weight,
         Total_phenols,
         Flavanoids,
         Nonflavanoid_phenols,
         Proanthocyanins,
         Color_intensity,
         Hue,
         diluted_wines,
         Proline,
         a
         ])

#用处理好的结果集生成excel
workbook = xlwt.Workbook(encoding='utf-8')
wine_sheet = workbook.add_sheet('Sheet 1', cell_overwrite_ok=True)
for i,row in enumerate(result):
    for j, col in enumerate(row):
        wine_sheet.write(i, j, col)

#保存到指定的路径
workbook.save('E:\\bigData\\dataset\\wine2.xls')

#打印结果集（可选）
print(result)

